Create the multi-column list box to contain the query results

The next step is to create the multi-column list box at the bottom of the form which will contain the results of the query.  Click the multi-column list box icon on the toolbar, then click anywhere within the Design Area.  Increase the size of the object (horizontally and vertically) to accommodate the data that will populate the object.  This is done by dragging the corners of the object to the desired locations.

As was done with the previously created multi-column list box, double-click on the new object, and select SQL statement, click the Build Query icon    to bring up the Build Query dialog box, then click Show Tables.

The columns in the report will contain the:

• employee last name
• employee first name
• order date
• product name
• sales amount (quantity x unit cost)

These items are located in several tables within the sample database (Employees, Orders, Order_Details, Products), thus the SQL query must contain a join to link the tables together.  Note that the sales amount does not exist within the database and must be calculated using the quantity and unit cost fields within the database.  The use of the Build Query dialog box to build the appropriate SQL query follows.

At this point the Build Query dialog box appears as shown below.  The next step is to select the tables to be included within the query.  Click Show Tables to see the list of tables.

Double-click the Employees, Orders, Order Details, and Products tables.  This moves the tables to the right where you can join them to each other.  Maximize the screen to provide space.  Drag the tables to position them as shown in the figure below.

The following joins need to be created:

• Employees.emp_id with Orders.employee-id
• Orders.transaction_id with Order_Details.transaction_id
• Order_Details.product_id with Product.product_id
  
  

This is done visually by selecting the field in one table, holding the mouse down, then dragging the mouse to the corresponding field in the other table.  Lines indicating the joins are then displayed as shown below:

Modifying the joins

To modify a join, right-click on the joining line. The following options will appear:

Selecting “Edit Join” will allow you to change the type of join.